package com.disys.database.connectivity;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.disys.common.ConfigurationLoader;

/**
 * @author Ubaid Raja
 * 
 */

public class DBConnectionManager implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = -7975539147799450168L;

	private static final transient Logger logger = Logger
			.getLogger(DBConnectionManager.class.getName());

	public static final int MYSQL = 1;
	public static final int SQLSERVER = 2;
	public static final int ORACLE = 3;
	private static int DB = SQLSERVER;
	private static Connection mysqlConn, sqlServerConn = null;

	private static DBConnectionManager connectionManager;

	private static Properties prop = null;
	private static String url = null;
	private static String user = null;
	private static String password = null;
	private static String driver = null;
	private static String databaseName = null;

	static {
		loadDBCredfromProp();
	}

	public static String getDatabaseName(Connection connection) {
		if (databaseName == null)
			connection = getConnection();
		return databaseName;
	}

//	public static String getLimitQuery(int limit, int offset, String orderby) {
//		String query = "";
//		if (databaseName.equalsIgnoreCase("MySQL")) {
//			query += " order by " + orderby + " desc limit " + limit
//					+ " offset " + offset;
//		}
//		// else if (databaseName.equalsIgnoreCase("Microsoft SQL Server")) {
//		/**
//		 * It needs different implementation so it is handeled in DAO class as
//		 * well
//		 */
//		// SELECT * FROM (
//		// SELECT *, ROW_NUMBER() OVER (ORDER BY pfjobid desc) as row FROM
//		// processflowjobs
//		// ) a WHERE row > 5 and row <= 10
//		// query += " rownum >= " + offset + " and rownum < (" + offset +
//		// limit+")";
//		// }
//		else if (databaseName.equalsIgnoreCase("Oracle")) {
//			query += " and rownum <= " + limit + " and rownum > " + offset
//					+ " order by " + orderby + " desc";
//		}
//		return query;
//	}

	public static void loadDBCredfromProp() {
		prop = ConfigurationLoader.getProperties();
		user = prop.getProperty("USERNAME_"+DB);
		password = prop.getProperty("PASSWORD_"+DB);
		url = prop.getProperty("DBURL_"+DB);
		driver = prop.getProperty("DRIVER_"+DB);
	}

	private DBConnectionManager() throws Exception {

		try {
			loadDBCredfromProp();
			Class.forName(driver);
			logger.info("DB Driver loaded for disys");
			if(DB == MYSQL)
			mysqlConn = DriverManager.getConnection(url, user, password);
			else if(DB == SQLSERVER)
				sqlServerConn = DriverManager.getConnection(url, user, password);
			else
				mysqlConn = DriverManager.getConnection(url, user, password);
			// logger.debug("Connection established ::" +
			// connection.getCatalog());
		} catch (ClassNotFoundException e) {
			logger.error(
					"Failed to connect with database, check the properties file for DB Driver",
					e);
		} catch (SQLException e) {
			logger.error(
					"Failed to connect with database, check the properties file for DB settings",
					e);
		} catch (Exception e) {
			logger.error(
					"Failed to connect with database, check the properties file for DB settings",
					e);
		}

	}

	public static Connection getConnection(int dbType) {
		// logger.debug("Getting connection from JDBC Connection Manager");
		if (dbType == 1)
			DB = MYSQL;
		else
			DB = dbType;
		try{
			if (DB == MYSQL && (mysqlConn == null || mysqlConn.isClosed())) {
				try {
					// logger.debug("Getting connection from JDBC Connection Manager");
					connectionManager = new DBConnectionManager();

				} catch (ClassNotFoundException e) {
					mysqlConn = null;
					logger.error("Failed to connect with database, ", e);
				} catch (SQLException e) {
					mysqlConn = null;
					logger.error("Failed to connect with database, ", e);
				} catch (Exception e) {
					mysqlConn = null;
					logger.error("Failed to connect with database, ", e);
				}
				return mysqlConn;
			} else if (DB == SQLSERVER
					&& (sqlServerConn == null || sqlServerConn.isClosed())) {
				try {
					// logger.debug("Getting connection from JDBC Connection Manager");
					connectionManager = new DBConnectionManager();

				} catch (ClassNotFoundException e) {
					sqlServerConn = null;
					logger.error("Failed to connect with database, ", e);
				} catch (SQLException e) {
					sqlServerConn = null;
					logger.error("Failed to connect with database, ", e);
				} catch (Exception e) {
					sqlServerConn = null;
					logger.error("Failed to connect with database, ", e);
				}
				return sqlServerConn;
			}
		}catch(Exception e){
			logger.error(e);
		}
		return mysqlConn;
	}

	public static Connection getConnection() {

		return getConnection(2);

	}

	public static void closeConnection(Connection connection) {
		logger.info("Closing JDBC Connection...");
		try {
			if (connection != null && !connection.isClosed()) {
				connection.close();
				logger.info("JDBC Connection closed...");
			} else
				logger.info("JDBC Connection already closed...");
		} catch (SQLException sqe) {
			connection = null;
			logger.error("Failed to connect with database, ", sqe);
		} catch (Exception e) {
			connection = null;
			logger.error("Failed to connect with database, ", e);
		}
		connection = null;
	}


	protected void finalize() throws Throwable {
		logger.debug("Closing JDBC Connection finalize() called...");
		super.finalize();
		try {
			if (mysqlConn != null) {
				closeConnection(mysqlConn);
			}
			if (sqlServerConn != null) {
				closeConnection(sqlServerConn);
			}
		} catch (Exception ex) {
			logger.error("In finalize()", ex);
		}
	}

	public static void main(String[] args) {
		try {
			System.out.println("Driver :" + driver + ", DBURL :" + url
					+ ", user :" + user + ", pass :" + password);
			Connection con = getConnection();
			//System.out.println("Query ::" + getLimitQuery(10, 5, "asdf"));
			System.out.println("Connection ok ::" + con.getCatalog());
			closeConnection(con);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
